# @Project: diablo
# @File: stock_related_init
# @Time: 2023/11/13 17:07
# @User: Jolly
# @Email: jolly@007vin.com

"""
初始化绑定库存关系
"""

# 初始化绑定库存关系

import pymysql
import time
import threading

from dbutils.pooled_db import PooledDB


lock = threading.Lock()


class StockRelatedInit:

    def __init__(self):

        start_time = time.time()

        self.quality_dict = {}

        # 初始化数据库连接

        self.mysql_conn = self.get_mysql_connection()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    def get_mysql_connection(self):
        maxconnections = 50  # 最大连接数
        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.21',
            user='users',
            port=3306,
            passwd='uSR@ll7GnJDopei',
            db='users',
            use_unicode=True)

        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='192.168.191.47',
        #     user='users',
        #     port=3306,
        #     passwd='uSR@#H^&JD',
        #     db='users',
        #     use_unicode=True)

        return pool

    def get_bind_merchant_data(self):
        """
        获取绑定了库存的商家
        """

        merchant_id_list = []

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select merchant_id, bind_merchant_stock, name, phone from diablo_merchant where status=1 and is_delete_time=0
             and bind_merchant_stock <> '' """

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:

                for result_ in result:
                    merchant_id_list.append({"merchant_id": result_[0], "bind_merchant_stock": result_[1],
                                             "name": result_[2], "phone": result_[3]})

        except Exception as e:
            print("获取用户分公司信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return merchant_id_list

    def get_merchant_stock_data(self, merchant_id_list):
        """
        根据绑定了库存的商家，找到对应的库存，然后组装写入
        """
        stock_merchant = []
        # remove_ids = ["3", "887afd6cdda14b6d8c5669bb94d15c02", "264329a4305b44f7bc8dccab3d9fe7ff"]
        remove_ids = ["666"]
        for merchant in merchant_id_list:
            if merchant.get("merchant_id") not in remove_ids:
                stock_merchant.append(merchant)

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            for stock in stock_merchant:

                sql = f"""select id, company_name, stock_erp, stock_identify, stock_key  from diablo_company where status=1 and merchant_id = '{stock.get("bind_merchant_stock")}' """

                cursor.execute(sql)

                result = cursor.fetchall()

                if len(result) > 0:

                    for result_ in result:
                        # class MerchantStockRelated(BaseModel):
                        #     """商家库存关联表，一对一库存关联：diablo_merchant、diablo_company"""
                        #     id = AutoField()
                        #     status = SmallIntegerField(default=1, help_text='1. 正常使用. 0. 删除')
                        #     merchant_id = CharField(max_length=32, help_text='商家id', index=True)
                        #     merchant_name = CharField(max_length=50, help_text='商家名称')
                        #     merchant_phone = CharField(max_length=11, help_text='商家手机号')
                        #     merchant_company_id = CharField(max_length=32, help_text='库存对接id', index=True)
                        #     company_name = CharField(max_length=50, help_text='库存对接名称')
                        #     erp_type = CharField(max_length=50, help_text='erp类型')
                        #     erp_uid = CharField(max_length=50, help_text='erp唯一id')
                        #     app_key = CharField(max_length=50, help_text='erp对接key')
                        #     goods_cst_id = CharField(max_length=32, help_text='绑定的厂商通商家id', index=True)
                        #     sort = SmallIntegerField(default=1, help_text='优先级排序')
                        #     create_time = DateTimeField(default=datetime.datetime.now)
                        #
                        #     class Meta:
                        #         table_name = 'diablo_stock_related'

                        if stock.get("bind_merchant_stock") in ("3", "887afd6cdda14b6d8c5669bb94d15c02", "264329a4305b44f7bc8dccab3d9fe7ff"):
                            if result_[3] != "T0000002_4TH0WP2OQ_T":
                                continue

                        key = ['merchant_id', 'merchant_name', 'merchant_phone', 'merchant_company_id', 'company_name',
                               'erp_type', 'erp_uid', 'app_key']

                        key_param = [stock.get('merchant_id'), stock.get('name'), stock.get('phone'), result_[0],
                                     result_[1], result_[2], result_[3], result_[4]]

                        value = ""

                        for i, v in enumerate(key):
                            value += '"{}",'.format(key_param[i])

                        values = ", ".join('{0}=values({0})'.format(k) for k in key)

                        sql = '''insert into diablo_stock_related ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                            ",".join(key), value[:-1], values)
                        # print(sql)
                        cursor.execute(sql)
                        mysql_conn.commit()

                        # 写入厂商通关系表
                        # class MerchantCSTRelated(BaseModel):
                        #     """商户互关联厂商通（商品表）一对多库存关联：diablo_merchant """
                        #     id = AutoField()
                        #     status = SmallIntegerField(default=1, help_text='1. 正常使用. 0. 删除')
                        #     merchant_id = CharField(max_length=32, help_text='商家id', index=True)
                        #     merchant_name = CharField(max_length=50, help_text='商家名称')
                        #     bind_merchant_id = CharField(max_length=32, help_text='被关联商家id', index=True)
                        #     bind_merchant_name = CharField(max_length=50, help_text='被关联商家名称')
                        #     sort = SmallIntegerField(default=1, help_text='优先级排序')
                        #     is_main = SmallIntegerField(default=1, help_text='是否主库存  1. 主. 0. 非主')
                        #
                        #     class Meta:
                        #         table_name = 'diablo_merchant_cst_related'

                        key = ['merchant_id', 'merchant_name', 'bind_merchant_id', 'bind_merchant_name']

                        key_param = [stock.get('merchant_id'), stock.get('name'),
                                     stock.get('merchant_id'), stock.get('name')]

                        value = ""

                        for i, v in enumerate(key):
                            value += '"{}",'.format(key_param[i])

                        values = ", ".join('{0}=values({0})'.format(k) for k in key)

                        sql = '''insert into diablo_merchant_cst_related ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                            ",".join(key), value[:-1], values)
                        # print(sql)
                        cursor.execute(sql)
                        mysql_conn.commit()

        except Exception as e:
            print("获取用户分公司信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return {}


if __name__ == "__main__":
    service = StockRelatedInit()
    merchant_data = service.get_bind_merchant_data()
    if merchant_data:
        service.get_merchant_stock_data(merchant_data)